from tortoise import BaseDBAsyncClient


async def upgrade(db: BaseDBAsyncClient) -> str:
    return """
        CREATE TABLE IF NOT EXISTS `categories` (
    `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '分类ID',
    `name` VARCHAR(50) NOT NULL COMMENT '分类名称',
    `type` VARCHAR(8) NOT NULL COMMENT '分类类型',
    `sort_order` INT NOT NULL COMMENT '排序顺序' DEFAULT 0,
    `icon` VARCHAR(255) COMMENT '图标URL',
    `created_at` DATETIME(6) NOT NULL COMMENT '创建时间' DEFAULT CURRENT_TIMESTAMP(6),
    `parent_id` INT COMMENT '父分类ID',
    CONSTRAINT `fk_categori_categori_56c95ac7` FOREIGN KEY (`parent_id`) REFERENCES `categories` (`id`) ON DELETE SET NULL
) CHARACTER SET utf8mb4 COMMENT='分类表';
CREATE TABLE IF NOT EXISTS `live_categories` (
    `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '直播分类ID',
    `name` VARCHAR(50) NOT NULL COMMENT '分类名称',
    `type` VARCHAR(21) NOT NULL COMMENT '分类类型',
    `sort_order` INT NOT NULL COMMENT '排序顺序' DEFAULT 0,
    `created_at` DATETIME(6) NOT NULL COMMENT '创建时间' DEFAULT CURRENT_TIMESTAMP(6)
) CHARACTER SET utf8mb4 COMMENT='直播分类表';
CREATE TABLE IF NOT EXISTS `locations` (
    `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '地点ID',
    `name` VARCHAR(100) NOT NULL COMMENT '地点名称',
    `type` VARCHAR(10) NOT NULL COMMENT '地点类型',
    `latitude` DECIMAL(10,8) COMMENT '纬度',
    `longitude` DECIMAL(11,8) COMMENT '经度',
    `address` VARCHAR(255) COMMENT '详细地址',
    `city` VARCHAR(50) COMMENT '城市',
    `province` VARCHAR(50) COMMENT '省份',
    `country` VARCHAR(50) NOT NULL COMMENT '国家' DEFAULT '中国',
    `description` LONGTEXT COMMENT '地点描述',
    `images` JSON COMMENT '图片列表',
    `rating` DECIMAL(3,2) NOT NULL COMMENT '评分' DEFAULT 0,
    `price_level` INT NOT NULL COMMENT '价格等级' DEFAULT 0,
    `opening_hours` LONGTEXT COMMENT '开放时间',
    `contact_phone` VARCHAR(20) COMMENT '联系电话',
    `website` VARCHAR(255) COMMENT '官方网站',
    `tags` JSON COMMENT '标签列表',
    `created_at` DATETIME(6) NOT NULL COMMENT '创建时间' DEFAULT CURRENT_TIMESTAMP(6),
    `updated_at` DATETIME(6) NOT NULL COMMENT '更新时间' DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    `status` VARCHAR(10) NOT NULL COMMENT '状态' DEFAULT 'active',
    `category_id` INT COMMENT '分类ID',
    CONSTRAINT `fk_location_categori_415c5eb9` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`) ON DELETE SET NULL
) CHARACTER SET utf8mb4 COMMENT='地点表';
CREATE TABLE IF NOT EXISTS `popular_itineraries` (
    `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '热门行程ID',
    `title` VARCHAR(200) NOT NULL COMMENT '行程标题',
    `description` LONGTEXT COMMENT '行程描述',
    `travel_days` INT NOT NULL COMMENT '旅行天数',
    `pace` VARCHAR(9) NOT NULL COMMENT '行程节奏' DEFAULT 'moderate',
    `budget_type` VARCHAR(8) NOT NULL COMMENT '预算类型' DEFAULT 'moderate',
    `tags` JSON COMMENT '标签',
    `view_count` INT NOT NULL COMMENT '浏览数' DEFAULT 0,
    `like_count` INT NOT NULL COMMENT '点赞数' DEFAULT 0,
    `is_active` BOOL NOT NULL COMMENT '是否激活' DEFAULT 1,
    `created_at` DATETIME(6) NOT NULL COMMENT '创建时间' DEFAULT CURRENT_TIMESTAMP(6)
) CHARACTER SET utf8mb4 COMMENT='热门行程参考表';
CREATE TABLE IF NOT EXISTS `users` (
    `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '用户ID',
    `username` VARCHAR(50) NOT NULL UNIQUE COMMENT '用户名',
    `email` VARCHAR(100) UNIQUE COMMENT '邮箱',
    `phone` VARCHAR(20) COMMENT '手机号',
    `password_hash` VARCHAR(255) NOT NULL COMMENT '密码哈希值',
    `avatar` VARCHAR(255) COMMENT '头像URL',
    `nickname` VARCHAR(50) COMMENT '昵称',
    `bio` LONGTEXT COMMENT '个人简介',
    `user_type` VARCHAR(6) NOT NULL COMMENT '用户类型' DEFAULT 'normal',
    `created_at` DATETIME(6) NOT NULL COMMENT '创建时间' DEFAULT CURRENT_TIMESTAMP(6),
    `updated_at` DATETIME(6) NOT NULL COMMENT '更新时间' DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    `last_login_at` DATETIME(6) COMMENT '最后登录时间',
    `status` VARCHAR(8) NOT NULL COMMENT '用户状态' DEFAULT 'active'
) CHARACTER SET utf8mb4 COMMENT='用户表';
CREATE TABLE IF NOT EXISTS `itineraries` (
    `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '行程ID',
    `title` VARCHAR(200) NOT NULL COMMENT '行程标题',
    `description` LONGTEXT COMMENT '行程描述',
    `travel_reason` LONGTEXT COMMENT '出行理由',
    `start_date` DATE COMMENT '开始日期',
    `end_date` DATE COMMENT '结束日期',
    `travel_days` INT NOT NULL COMMENT '旅行天数' DEFAULT 1,
    `pace` VARCHAR(9) NOT NULL COMMENT '行程节奏' DEFAULT 'moderate',
    `budget_type` VARCHAR(8) NOT NULL COMMENT '预算类型' DEFAULT 'moderate',
    `preference_tags` JSON COMMENT '偏好标签',
    `is_public` BOOL NOT NULL COMMENT '是否公开' DEFAULT 0,
    `created_at` DATETIME(6) NOT NULL COMMENT '创建时间' DEFAULT CURRENT_TIMESTAMP(6),
    `updated_at` DATETIME(6) NOT NULL COMMENT '更新时间' DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    `user_id` INT NOT NULL COMMENT '用户ID',
    CONSTRAINT `fk_itinerar_users_cc17f0e9` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) CHARACTER SET utf8mb4 COMMENT='行程表';
CREATE TABLE IF NOT EXISTS `itinerary_locations` (
    `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '行程地点ID',
    `day_number` INT NOT NULL COMMENT '第几天',
    `sort_order` INT NOT NULL COMMENT '排序顺序' DEFAULT 0,
    `visit_duration` INT COMMENT '参观时长(分钟)',
    `notes` LONGTEXT COMMENT '备注',
    `created_at` DATETIME(6) NOT NULL COMMENT '创建时间' DEFAULT CURRENT_TIMESTAMP(6),
    `itinerary_id` INT NOT NULL COMMENT '行程ID',
    `location_id` INT NOT NULL COMMENT '地点ID',
    CONSTRAINT `fk_itinerar_itinerar_aab0795f` FOREIGN KEY (`itinerary_id`) REFERENCES `itineraries` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_itinerar_location_eddb1073` FOREIGN KEY (`location_id`) REFERENCES `locations` (`id`) ON DELETE CASCADE
) CHARACTER SET utf8mb4 COMMENT='行程地点关联表';
CREATE TABLE IF NOT EXISTS `live_streams` (
    `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '直播ID',
    `title` VARCHAR(200) NOT NULL COMMENT '直播标题',
    `description` LONGTEXT COMMENT '直播描述',
    `cover_image` VARCHAR(255) COMMENT '封面图片',
    `stream_url` VARCHAR(500) NOT NULL COMMENT '直播流地址',
    `status` VARCHAR(9) NOT NULL COMMENT '直播状态' DEFAULT 'scheduled',
    `viewer_count` INT NOT NULL COMMENT '观看人数' DEFAULT 0,
    `start_time` DATETIME(6) COMMENT '开始时间',
    `end_time` DATETIME(6) COMMENT '结束时间',
    `created_at` DATETIME(6) NOT NULL COMMENT '创建时间' DEFAULT CURRENT_TIMESTAMP(6),
    `updated_at` DATETIME(6) NOT NULL COMMENT '更新时间' DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    `category_id` INT COMMENT '分类ID',
    `streamer_id` INT NOT NULL COMMENT '主播ID',
    CONSTRAINT `fk_live_str_live_cat_ff587e65` FOREIGN KEY (`category_id`) REFERENCES `live_categories` (`id`) ON DELETE SET NULL,
    CONSTRAINT `fk_live_str_users_a290f3cd` FOREIGN KEY (`streamer_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) CHARACTER SET utf8mb4 COMMENT='直播表';
CREATE TABLE IF NOT EXISTS `search_records` (
    `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '搜索记录ID',
    `keyword` VARCHAR(100) NOT NULL COMMENT '搜索关键词',
    `search_type` VARCHAR(7) NOT NULL COMMENT '搜索类型' DEFAULT 'general',
    `location_type` VARCHAR(10) NOT NULL COMMENT '地点类型' DEFAULT 'all',
    `latitude` DECIMAL(10,8) COMMENT '纬度',
    `longitude` DECIMAL(11,8) COMMENT '经度',
    `search_results_count` INT NOT NULL COMMENT '搜索结果数量' DEFAULT 0,
    `created_at` DATETIME(6) NOT NULL COMMENT '创建时间' DEFAULT CURRENT_TIMESTAMP(6),
    `user_id` INT COMMENT '用户ID',
    CONSTRAINT `fk_search_r_users_94375cbc` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) CHARACTER SET utf8mb4 COMMENT='搜索记录表';
CREATE TABLE IF NOT EXISTS `third_party_logins` (
    `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '第三方登录ID',
    `platform` VARCHAR(8) NOT NULL COMMENT '平台类型',
    `open_id` VARCHAR(255) NOT NULL COMMENT '开放ID',
    `union_id` VARCHAR(255) COMMENT '联合ID',
    `access_token` VARCHAR(255) COMMENT '访问令牌',
    `refresh_token` VARCHAR(255) COMMENT '刷新令牌',
    `expires_at` DATETIME(6) COMMENT '过期时间',
    `created_at` DATETIME(6) NOT NULL COMMENT '创建时间' DEFAULT CURRENT_TIMESTAMP(6),
    `updated_at` DATETIME(6) NOT NULL COMMENT '更新时间' DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    `user_id` INT NOT NULL COMMENT '用户ID',
    CONSTRAINT `fk_third_pa_users_236e7f19` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) CHARACTER SET utf8mb4 COMMENT='第三方登录表';
CREATE TABLE IF NOT EXISTS `wishlists` (
    `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '心愿单ID',
    `notes` LONGTEXT COMMENT '备注',
    `priority` INT NOT NULL COMMENT '优先级' DEFAULT 1,
    `created_at` DATETIME(6) NOT NULL COMMENT '创建时间' DEFAULT CURRENT_TIMESTAMP(6),
    `location_id` INT NOT NULL COMMENT '地点ID',
    `user_id` INT NOT NULL COMMENT '用户ID',
    CONSTRAINT `fk_wishlist_location_5f596a64` FOREIGN KEY (`location_id`) REFERENCES `locations` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_wishlist_users_3a286c8c` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) CHARACTER SET utf8mb4 COMMENT='心愿单表';
CREATE TABLE IF NOT EXISTS `aerich` (
    `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `version` VARCHAR(255) NOT NULL,
    `app` VARCHAR(100) NOT NULL,
    `content` JSON NOT NULL
) CHARACTER SET utf8mb4;"""


async def downgrade(db: BaseDBAsyncClient) -> str:
    return """
        """
